import os
import pymysql
import logging

# 设置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# 数据库连接配置
DB_CONFIG = {
    'host': '150.109.6.243',
    'user': 'music_ocr_db',
    'password': 'wZktkPy4X2TpZcCY',
    'db': 'music_ocr_db',
    'charset': 'utf8mb4',
}

def execute_sql_file(filepath):
    """执行SQL文件中的所有语句"""
    try:
        # 读取SQL文件内容
        with open(filepath, 'r', encoding='utf-8') as f:
            sql_content = f.read()
        
        # 将内容拆分为单独的SQL语句
        sql_statements = sql_content.split(';')
        
        # 连接数据库并执行语句
        connection = pymysql.connect(**DB_CONFIG)
        try:
            with connection.cursor() as cursor:
                for statement in sql_statements:
                    statement = statement.strip()
                    if statement:  # 忽略空语句
                        logger.info(f"执行SQL: {statement[:100]}...")
                        cursor.execute(statement)
                connection.commit()
                logger.info("所有SQL语句执行成功")
        finally:
            connection.close()
        
        return True
    except Exception as e:
        logger.error(f"执行SQL文件时出错: {str(e)}")
        return False

def check_tables_exist():
    """检查聊天相关的表是否已存在"""
    tables_to_check = ['conversations', 'chat_messages']
    
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            for table in tables_to_check:
                cursor.execute(f"SHOW TABLES LIKE '{table}'")
                if not cursor.fetchone():
                    return False
        return True
    except Exception as e:
        logger.error(f"检查表时出错: {str(e)}")
        return False
    finally:
        connection.close()

def main():
    """主函数"""
    # 检查表是否已存在
    if check_tables_exist():
        logger.info("聊天相关的表已存在，无需创建")
        return True
    
    # 获取SQL文件路径
    current_dir = os.path.dirname(os.path.abspath(__file__))
    sql_file = os.path.join(current_dir, 'sql', 'create_chat_tables.sql')
    
    # 检查SQL文件是否存在
    if not os.path.exists(sql_file):
        logger.error(f"SQL文件不存在: {sql_file}")
        return False
    
    # 执行SQL文件
    return execute_sql_file(sql_file)

if __name__ == "__main__":
    success = main()
    if success:
        logger.info("数据库更新成功")
    else:
        logger.error("数据库更新失败") 